3/17/2008 – Touch/Peel/Stand, then Try/Catch/Raise

I heard one of my all-time favorite songs today on the radio: "Touch, Peel, and Stand", by Days of the New.

Then I was looking at some SQL 2005 code that recently came in handy for me at a client site – specifically, the ability to implement a TRY/CATCH block inside a stored procedure, and RAISE an error back to the calling application.

Now, the fact that you can insert TRY/CATCH code into SQL Server 2005 stored procedures isn't exactly "hot off the presses" news – most people have been aware of it for some time. Additionally, the ability to raise an error (using RAISERROR) goes back to SQL Server 2000.

But what's cool is that you can combine the two, to check for an error, grab information for that error, and then raise that error back to the application. So if you're also doing a TRY/CATCH in your application, you can access the error message that was raised in the SQL RAISERROR call.

So first, let's look at a very simple .NET call to a basic stored procedure using TRY/CATCH block. The stored proc will also have a TRY/CATCH block, and will raise any error back to the data provider, so that we can catch the error in .NET.

SqlConnectionStringBuilder osb = new SqlConnectionStringBuilder();
osb.DataSource = "localHost";
osb.IntegratedSecurity = true;
osb.InitialCatalog = "AdventureWorks";

SqlConnection oConn = new SqlConnection(osb.ConnectionString);
SqlCommand oCmd = new SqlCommand("dbo.TestSPRaiseError", oConn);
oConn.Open();
try
{
    oCmd.ExecuteNonQuery();
}
catch (Exception eErr)
{
    // Either write out the error, or just display to the screen
    MessageBox.Show("Error occured!\n\n" + eErr.Message.ToString());
}

Now, here's the SQL code, a test stored procedure that intentionally tries to violate a database constraint by deleting an order header ID for which line items exist.

CREATE PROCEDURE dbo.TestSPRaiseError
AS

BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION

    BEGIN TRY
        -- Try to do something that will generate an error
        DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 6
    END TRY
    BEGIN CATCH
        DECLARE @errorseverity int, @errornumber int,
            @errormessage nvarchar(4000), @errorstate int,
            @ErrorLine int, @ErrorProc nvarchar(200)
        -- Grab error information from SQL functions
        SET @errorseverity = ERROR_SEVERITY()
        SET @errornumber = ERROR_NUMBER()
        SET @errorMessage = ERROR_MESSAGE()
        SET @errorstate = ERROR_STATE()
        SET @ErrorLine = ERROR_LINE()
        SET @ErrorProc = ERROR_PROCEDURE()
        -- Construct a detailed error message to send back
        SET @ErrorMessage= 'Error ' + CAST(@ErrorNumber AS VARCHAR(10)) +
                    ' in procedure: ' + @ErrorProc +
                    ' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +
                    ' Error text: ' + @ErrorMessage

        -- Not all errors generate an error state, to set to 1 if it's zero
        IF @ERRORSTATE = 0
            SET @ERRORSTATE = 1

        RAISERROR (@errormessage , @ERRORSEVERITY, @ERRORSTATE, @ERRORNUMBER)

        -- If the error renders the transaction as uncommittable, we must rollback
        IF XACT_STATE() < 0
            ROLLBACK TRANSACTION
    END CATCH
    COMMIT TRANSACTION
END
GO

Again, nothing earth-shattering, but hopefully a nice-to-know. There are many ways you could construct the error message and then deal with it in the client piece.

I just added Touch, Peel, and Stand to my "Midnight Mix" on my MP3 player. It's 3 AM here on the east coast, but I guess that means it's midnight somewhere!

KG

 

Comments

[IANinja]

If you want a good DOTN song, try out The Down Town. I think I heard they are back together again.